# encoding:utf-8
import csv
import sys
import json
from SampleDataHeng.DBUtils import DBUtils
reload(sys)
sys.setdefaultencoding( "utf-8" )
config_file=file('config.json')
config_json=json.load(config_file)
database_host=config_json['host']
database_port=config_json['port']
database_user=config_json['user']
database_password=config_json['password']
database=config_json['database']
database_tables=config_json['tables']
database_charset=config_json['charset']
# 连接数据库获取表的结构信息
dbutil= DBUtils()
conn,cur=dbutil.connDB(host=database_host,port=database_port, user=database_user,password=database_password,database=database,charset=database_charset)
# 如果一个表的名字都没有传入，则把数据库所有表都导出
if database_tables==[]:
    query_table_sql="""
    SELECT 
      table_name 
    FROM
      information_schema.tables 
    WHERE table_schema = 
    """
    query_table_sql=query_table_sql+" '"+database+"' "
    query_table_sql=query_table_sql+"AND table_type = 'base table' "
    eff_row,cur=dbutil.exeQuery(cur,query_table_sql)
    result_tables=cur.fetchall()
    for r in result_tables:
        database_tables.append(r[0])
for table in database_tables:
    # 获取表结构信息
    meta_data_sql="""
    SELECT  column_name,
     column_comment FROM information_schema.columns WHERE table_schema =
    """
    meta_data_sql=meta_data_sql+"'"+database+"'"
    meta_data_sql=meta_data_sql+"AND table_name = "
    meta_data_sql = meta_data_sql + "'" + table + "';"
    eff_row,cur=dbutil.exeQuery(cur,meta_data_sql)
    columns=[] #存储列名
    column_comments=[] #存储列注释
    result=cur.fetchall()
    for record in result:
        columns.append(record[0])
        column_comments.append(record[1])
    # 根据表结构信息获取表记录的前5条
    data_sql="select "
    for column_index in range(0,len(columns)):
        if column_index!=len(columns)-1:
            data_sql=data_sql+" `"+columns[column_index]+"`,"
        else:
            data_sql = data_sql + " `" + columns[column_index] + "` "
    data_sql=data_sql+" from "
    data_sql = data_sql +"`"+database+"`.`"+table+"` "
    data_sql = data_sql +"limit 0,5"
    print data_sql
    eff_row,cur=dbutil.exeQuery(cur,data_sql)
    data_result=cur.fetchall()
    # 写入文件
    # 首先生成列名，列名为：“英文列名（中文解释）”
    fieldnames=[]
    for i in range(0,len(columns)):
        fieldnames.append(columns[i]+"("+column_comments[i]+")")
    # 首先在csv文件中写入列名
    with open('csv/'+table+'.csv', 'w') as csvfile:
        writer = csv.writer(csvfile, dialect='excel')
        writer.writerow(fieldnames)
        # 写入表数据
        for d_r in data_result:
            writer.writerow(d_r)